﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics.CodeAnalysis;
using System.Globalization;
using System.Linq;
using System.Threading;
using Loki.Resources;
using Loki.Utils;

namespace Loki.Data.SqlServer
{
    /// <summary>
    /// SQL Server database adapter.
    /// </summary>
    public class SqlDatabaseAdapter : LokiObject, IDatabaseAdapter
    {
        public virtual void Initialize()
        {
        }

        public bool Initialized { get; protected set; }

        public string ConnectionString
        {
            get;
            set;
        }

        public string CurrentUser
        {
            get
            {
                return Environment.UserName;
            }
        }

        public virtual IDatabaseCommand CreateCommand(string P_Name)
        {
            return new SqlDatabaseCommand(P_Name);
        }

        public virtual IDatabaseCommand CreateCommand(string P_Name, CommandType P_Type)
        {
            return new SqlDatabaseCommand(P_Name, P_Type);
        }

        #region IDatabaseAdapter Members

        private string _DataSouceDescription = string.Empty;

        private const string DESCRIPTION_REQUEST = "select @@SERVERNAME as Server, @@VERSION as Version, DB_NAME() as DB";

        private const string DESCRIPTION_FORMAT = "{0} - {1} ({2})";

        public string DataSourceDescription
        {
            get
            {
                if (!string.IsNullOrEmpty(_DataSouceDescription))
                {
                    try
                    {
                        using (var L_Transaction = OpenTransaction())
                        {
                            var L_Command = CreateCommand(DESCRIPTION_REQUEST, CommandType.Text);
                            using (IDataReader L_Reader = ExecuteQuery(L_Command, L_Transaction))
                            {
                                L_Reader.Read();
                                string L_DataSouceDescription = string.Format(CultureInfo.InvariantCulture, DESCRIPTION_FORMAT, L_Reader[0], L_Reader[1], L_Reader[2]);
                                L_DataSouceDescription = _DataSouceDescription.Replace(Environment.NewLine, string.Empty);
                                Interlocked.CompareExchange(ref _DataSouceDescription, L_DataSouceDescription, string.Empty);
                            }
                        }
                    }
                    catch (Exception Ex)
                    {
                        Log.Fatal(LogStrings.Data_DatabaseAdapters_ConnectError, Ex);
                        throw;
                    }
                }

                return _DataSouceDescription;
            }
        }

        /// <summary>
        /// Executes command.
        /// </summary>
        /// <param name="P_Command">The command.</param>
        /// <param name="P_Transaction">The transaction.</param>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "No user request in the table parameter")]
        public virtual void ExecuteNonQuery(IDatabaseCommand P_Command, IDbTransaction P_Transaction)
        {
            using (SqlCommand L_Command = new SqlCommand())
            {
                L_Command.CommandText = P_Command.Name;
                L_Command.Transaction = P_Transaction as SqlTransaction;
                L_Command.Connection = L_Command.Transaction.Connection;
                L_Command.Parameters.AddRange(P_Command.Parameters.ToArray());
                L_Command.CommandType = P_Command.Type;

                L_Command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Executes the query.
        /// </summary>
        /// <param name="P_Command">The command.</param>
        /// <param name="P_Transaction">The transaction.</param>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "No user request in the table parameter")]
        public virtual IDataReader ExecuteQuery(IDatabaseCommand P_Command, IDbTransaction P_Transaction)
        {
            using (SqlCommand L_Command = new SqlCommand())
            {
                L_Command.CommandText = P_Command.Name;
                L_Command.Transaction = P_Transaction as SqlTransaction;
                L_Command.Connection = L_Command.Transaction.Connection;
                L_Command.Parameters.AddRange(P_Command.Parameters.ToArray());
                L_Command.CommandType = P_Command.Type;

                return L_Command.ExecuteReader();
            }
        }

        /// <summary>
        /// Executes the query and return an unique value.
        /// </summary>
        /// <param name="P_Command">The command.</param>
        /// <param name="P_Transaction">The transaction.</param>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "No user request in the table parameter")]
        public virtual object ExecuteScalar(IDatabaseCommand P_Command, IDbTransaction P_Transaction)
        {
            using (SqlCommand L_Command = new SqlCommand())
            {
                L_Command.CommandText = P_Command.Name;
                L_Command.Transaction = P_Transaction as SqlTransaction;
                L_Command.Connection = L_Command.Transaction.Connection;
                L_Command.Parameters.AddRange(P_Command.Parameters.ToArray());
                L_Command.CommandType = P_Command.Type;

                return L_Command.ExecuteScalar();
            }
        }

        private const string SELEC_ALL = "SELECT * FROM {0}";

        /// <summary>
        /// Bulks insert data in a table of the same name.
        /// </summary>
        /// <param name="P_Table">The table.</param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "No user input")]
        public int BulkInsert(DataTable P_Table)
        {
            if (P_Table != null)
            {
                using (SqlDataAdapter L_Adapter = new SqlDataAdapter(string.Format(CultureInfo.InvariantCulture, SELEC_ALL, P_Table), ConnectionString))
                {
                    SqlCommandBuilder L_Builder = new SqlCommandBuilder(L_Adapter);
                    try
                    {
                        L_Builder.GetInsertCommand();
                        return L_Adapter.Update(P_Table);
                    }
                    finally
                    {
                        L_Builder.Dispose();
                    }
                }
            }
            else
            {
                return 0;
            }
        }

        #endregion

        #region Transactions
        /// <summary>
        /// Open transaction on current database (new connection opened).
        /// </summary>
        /// <returns>New transaction.</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope", Justification = "Can not dispose the connection while transaction is alive")]
        public IDbTransaction OpenTransaction()
        {
            SqlConnection L_Conn = new SqlConnection(ConnectionString);
            try
            {
                L_Conn.Open();
                return L_Conn.BeginTransaction(IsolationLevel.ReadCommitted);
            }
            catch
            {
                if (L_Conn != null)
                {
                    L_Conn.Dispose();
                }

                throw;
            }
        }

        /// <summary>
        /// Commits the transaction.
        /// </summary>
        /// <param name="P_Tran">Transaction to commit.</param>
        public bool CommitTransaction(IDbTransaction P_Tran)
        {
            SqlConnection L_Conn = P_Tran.Connection as SqlConnection;
            P_Tran.Commit();
            if (L_Conn != null)
            {
                L_Conn.Dispose();
            }

            return true;
        }

        /// <summary>
        /// Rollbacks the transaction.
        /// </summary>
        /// <param name="P_Tran">Transaction to rollback.</param>
        public bool RollbackTransaction(IDbTransaction P_Tran)
        {
            if (P_Tran != null)
            {
                SqlConnection L_Conn = P_Tran.Connection as SqlConnection;
                P_Tran.Rollback();
                if (L_Conn != null)
                {
                    L_Conn.Dispose();
                }

                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
    }
}
